package com.qf.studentmanager.dao;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.qf.studentmanager.model.Clazz;
import com.qf.studentmanager.model.Grade;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClazzDao {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
QueryRunner queryRunner = new QueryRunner(comboPooledDataSource);

    public Long getTotal(String gid) {
//        String sql ="select count(1) from clazz;";
        StringBuffer sql = new StringBuffer("select count(1) from clazz");
        if (gid != null && !"".equals(gid)){
            sql.append(" where gid = ?");
        }

        try {
            if (gid != null && !"".equals(gid)){

                return queryRunner.query(sql.toString(), new ScalarHandler<>(),gid);
            }else {
                return queryRunner.query(sql.toString(), new ScalarHandler<>());

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0L;
    }

    public List<Clazz> getClazzByPage(Integer page, Integer rows, String sort, String oder, String gid) {
        List<Clazz> list = new ArrayList<>();

        StringBuffer sql = new StringBuffer("select * from clazz c inner join grade g on c.gid = g.gid");
        if (gid != null && !"".equals(gid)){
            sql.append(" where g.gid = ? order by "+sort+" "+oder+" limit ?,?;");
        }else {
            sql.append(" order by "+sort+" "+oder+" limit ?,?;");
        }
        try {

            //自定义返回集合。自行创建一个并接list。
            ResultSetHandler<List<Clazz>> rsh = new ResultSetHandler<List<Clazz>>() {
                @Override
                public List<Clazz> handle(ResultSet resultSet) throws SQLException {
                    while (resultSet.next()) {
                        int cid = resultSet.getInt("cid");
                        String clazzName = resultSet.getString("clazzName");
                        int gid1 = resultSet.getInt("gid");
                        String gradeName = resultSet.getString("gradeName");
                        Clazz clazz = new Clazz();
                        clazz.setCid(cid);
                        clazz.setClazzName(clazzName);
                        clazz.setGid(gid1);
                        Grade grade = new Grade();
                        grade.setGid(gid1);
                        grade.setGradeName(gradeName);
                        clazz.setGrade(grade);
                        list.add(clazz);
                    }
                    return list;
                }
            };

            //运行查询页面数据sql
            if (gid != null && !"".equals(gid)){
                return queryRunner.query(sql.toString(), rsh,gid,page,rows);
            }else {
                return queryRunner.query(sql.toString(), rsh,page,rows);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Clazz getClazzByName(String clazzName) {

        try {
            return queryRunner.query("select * from clazz where clazzName = ?;", new BeanHandler<>(Clazz.class),clazzName);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public Integer addClazz(Clazz clazz) throws SQLException {
        return   queryRunner.update("insert into  clazz(clazzName, gid) values (? ,?);", clazz.getClazzName(), clazz.getGid());
    }

    public List<Clazz> getClazzByGid(String gid) {
        try {
            return queryRunner.query("select * from clazz where  gid = ?", new BeanListHandler<>(Clazz.class),gid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Integer deleteClazzByCid(String cid) {
        try {
            return queryRunner.update("delete  from clazz where  cid = ?", cid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }
}
